from database import Ui_Form
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import QWidget,QMessageBox
from PyQt5.QtGui import QIcon
import pymssql
import sys

# import os
# if hasattr(sys, 'frozen'):
#     os.environ['PATH'] = sys._MEIPASS + ";" + os.environ['PATH']

class Mywindow(QWidget,Ui_Form):
    def __init__(self,parent=None):
        super(Mywindow, self).__init__(parent)
        self.setupUi(self)
        self.retranslateUi2(self)
        self.setWindowTitle('VO生成工具')
        

    def retranslateUi2(self, Form):
        self.lineEdit.setText('localhost')
        self.lineEdit_3.setText('sa')
        self.lineEdit_4.setText('123456')

        #连接
        def conn():
            if self.comboBox.currentText() != '':
                return
            self.server = self.lineEdit.text()
            self.name = self.lineEdit_3.text()
            self.password = self.lineEdit_4.text()
            try:
               
                self.conn = pymssql.connect(self.server, self.name, self.password) #服务器名,账户,密码,数据库名
                if self.conn:
                    self.cursor = self.conn.cursor() # 获取光标
                    sql = "select name from master.dbo.SysDatabases ORDER BY name"
                    self.cursor.execute(sql)
                    for row in self.cursor:
                        dbname = row[0]
                        if dbname != 'master' and dbname != 'tempdb' and dbname != 'model' and dbname != 'msdb' and dbname != 'ReportServer' and dbname != 'ReportServerTempDB':
                            self.comboBox.addItem(dbname)
                    self.showBox('连接成功','连接成功')

            except  Exception as e:
                self.showBox('连接失败',"服务器或账户或密码错误！")
        self.pushButton_2.clicked.connect(conn)

        #点击输出
        def output():
            #判断当前的表（）
            nowtablename = self.comboBox_2.currentText()
            if  nowtablename == '':
                #输出所有表
                size = self.comboBox_2.count()
                for i in range(size):
                    tablename = self.comboBox_2.itemText(i)
                    if tablename != '':
                        self.writeFile(tablename)
                self.showBox('输出成功','输出成功')
            else :
                self.writeFile(nowtablename)
                self.showBox('输出成功','输出成功')
                
                

        self.pushButton.clicked.connect(output)
        
        #数据库下拉框改变
        self.comboBox.currentIndexChanged.connect(self.selectionchange)


    def showBox(self,tip,content):
        QMessageBox.information(self, tip, content, QMessageBox.Yes)

    def writeFile(self,tablename):
        try:
            fileName = 'VO_'+tablename.capitalize()
            fh = open(fileName+'.java', 'w',encoding='utf8')
            fh.write('public class '+fileName+'{\n')

            sql = "select b.name 字段名,c.name 字段类型 from sysobjects a,syscolumns b,systypes c where a.id=b.id and a.name='"+tablename+"' and a.xtype='U' and b.xtype=c.xtype and c.name <> 'sysname' order by b.name"
            self.cursor.execute(sql)
            list = []
            
            for row in self.cursor:
                filedname = row[0]
                filedtype = row[1]
                # 判断类型
                if filedtype == 'int' or filedtype == 'smallint':
                    filedtype = 'Integer'
                elif filedtype == 'datetime':
                    filedtype = 'String'
                elif filedtype == 'decimal' or filedtype == 'float' or filedtype == 'numeric':
                    filedtype = 'Double'
                elif filedtype == 'nvarchar' or filedtype == 'sysname' or filedtype == 'nvarchar2' or filedtype == 'varchar' or filedtype == 'char':
                    filedtype = 'String'
                elif  filedtype == 'timestamp':
                    filedtype = 'Timestamp'
                
                tuple = (filedtype,filedname)
                list.append(tuple)
            #写入字段
            for i in list:
                filedtype = i[0]
                filedname = i[1]
                fh.write('\t'+filedtype +' '+ filedname + ' = null; \n')
            for i in list:
                fh.write('\n')
                filedtype = i[0]
                filedname = i[1]
                #写入方法
                fh.write('\tpublic '+filedtype+' get'+filedname.capitalize()+'(){\n\t\t return '+filedname+';\n\t}\n')
                fh.write('\tpublic void set'+filedname.capitalize()+'('+filedtype+' '+filedname+'){\n\t\tthis.'+filedname+' = '+filedname+';\n\t}')

            fh.write('\n')
            fh.write('VO_'+tablename.capitalize()+' '+tablename + '= new VO_'+tablename.capitalize()+'();')
            for i in list:
                #使用数据
                fh.write('\n')
                filedtype = i[0]
                filedname = i[1]
                if filedtype == 'String':
                    fh.write(tablename+'.set'+filedname.capitalize()+'("");')
                else:
                    fh.write(tablename+'.set'+filedname.capitalize()+'();')
            
            fh.write('\n}')
            fh.close()
                
        except  Exception as e:
            self.showBox('输出失败',str(e))
            fh.close()
    #当下拉索引发生改变时发射信号触发绑定的事件
    #选择数据库
    def selectionchange(self,i):
        #清空表名
        # for i in range(self.comboBox_2.)
        self.comboBox_2.clear()
        #currentText()：返回选中选项的文本
        nowdatabase = self.comboBox.currentText()
        #查询选中数据库的所有表名
        self.conn = pymssql.connect(self.server, self.name, self.password, nowdatabase) #服务器名,账户,密码,数据库名
        self.cursor = self.conn.cursor() # 获取光标
        sql = "select name from sysobjects where xtype='U' ORDER BY name";
        self.cursor.execute(sql)
        self.comboBox_2.addItem('')
        for row in self.cursor:
            tablename = row[0]
            self.comboBox_2.addItem(tablename)
    
    
        
    
    
if __name__ == '__main__' :
    app = QtWidgets.QApplication(sys.argv)
    app.setWindowIcon(QIcon(r'icon.ico'))
    window = Mywindow()
    window.show()
    sys.exit(app.exec())